package com.jf.StudenManage.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.jf.StudenManage.common.DataTools;
import com.jf.StudenManage.pojo.Student;

public class StudentDao {
     
	public   Student  queryStudentByName(String  username) {
		 String  sql="select  * from  student where   name='"+username+"'";
		 return  resolveResutSet(sql);	
	}
	public   Student  queryStudentByTelephone(String   telephone){
		String  sql="select  * from  student where   telephone='"+telephone+"'";
		 return  resolveResutSet(sql);
	}
	
	public   Student    queryStudentBySid(String  sid){
		String  sql="select  * from  student where   sid="+sid+" " ;
		return   resolveResutSet(sql);
	}
	
	
	public   void  insertStudent(String sql){
		DataTools.excuteSql(sql);
	}
	
	public   ArrayList<Student>  queryAll(){
		String  sql="select  * from  student";
		return   resolveResutsSet(sql);
	}
	
	public   void   delete(String  sid){
		String  sql="delete  from  student  where  sid="+sid;
		DataTools.excuteSql(sql);
	}
	
	public   void   update(Student student){
		String  sql="update   student  set  name='"+student.getName()+"', password='"+student.getPassword()+"',telephone='"+student.getTelephone()+"',gender='"+student.getGender()+"',sno='"+student.getSno()+"'  where    sid="+student.getSid()+"               ";
		DataTools.excuteSql(sql);
	}
	
	public   int  backCounts(){
		String  sql="select  count(*) as a  from   student";
		ResultSet  rs=DataTools.excuteQuery(sql);
		int  result=0;
		try {
			if(rs.next()){
				result= rs.getInt("a");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return   result;
	}
	
	public  ArrayList<Student>   queryByPagion(String pageNum){
		//limit语句是跟随  在sql语句的最后端出现  后面可以跟随  order by  group  by 等等
		//limit 语句用法是这样的：  select *   from   tablename    limit   a,b  从 第a索引位置 数据开始拿数据 拿b条数据   
		//分页时候的索引位置 默认是从  0开始的
		//前端传递过来的pageNum 跟 我们分页时候  对应的 a,b 的关系是：
		// limt  a,b       a=(pageNum-1)*b
		  int b=3;
		 int  a=(Integer.parseInt(pageNum)-1)*b;                                         
		String  sql="select  *  from  student  limit "+a+","+b;
		return   resolveResutsSet(sql);
	}
	
	
	
	
	
	
	public   Student   resolveResutSet(String  sql)  {
		ResultSet  rs=DataTools.excuteQuery(sql);
		Student  student=null;
		try {
			if(rs.next()){
				student=new   Student();
				student.setSid(rs.getInt("sid"));
			    student.setName(rs.getString("name"));
				student.setPassword(rs.getString("password"));
				student.setGender(rs.getString("gender"));
				student.setSno(rs.getString("sno"));
				student.setTelephone(rs.getString("telephone"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return  student;
	}
	
	public   ArrayList<Student>   resolveResutsSet(String  sql)    {
	   ArrayList<Student>  list=new  ArrayList<Student>();
	   ResultSet  rs=DataTools.excuteQuery(sql);
       Student student=null;
       try {
		while(rs.next()){
			   student=new   Student();
				student.setSid(rs.getInt("sid"));
			    student.setName(rs.getString("name"));
				student.setPassword(rs.getString("password"));
				student.setGender(rs.getString("gender"));
				student.setSno(rs.getString("sno"));
				student.setTelephone(rs.getString("telephone"));
				list.add(student);
		   }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  return  list;
	}
	
	
	
	
}
